﻿using DataAchive.Common;
using DataAchive.Model.ViewModels;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;

namespace DataAchive.Helper
{
    public static class ExcelHelper
    {

        /// <summary>
        /// 导出实体为文档
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="t"></param>
        public static void Export<T>(string filePath, List<T> t, ObservableCollection<string> classIfyPriority = null, string SheetName = "Sheet1")
        {
            IWorkbook excelDoc = new XSSFWorkbook();
            //创建一个Sheet
            ISheet excelSheet = excelDoc.CreateSheet(SheetName);


            Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            // 写入表头
            IRow headerRow = excelSheet.CreateRow(0);
            for (int i = 0; i < propertyInfos.Length; i++)
            {
                DescriptionAttribute columnAttribute = propertyInfos[i].GetCustomAttribute<DescriptionAttribute>();
                if (columnAttribute != null)
                {
                    headerRow.CreateCell(i).SetCellValue(columnAttribute.Description);
                }
            }

            // 写入数据
            int rowIndex = 1;
            foreach (var item in t)
            {
                IRow dataRow = excelSheet.CreateRow(rowIndex);
                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    DescriptionAttribute columnAttribute = propertyInfos[i].GetCustomAttribute<DescriptionAttribute>();
                    if (columnAttribute != null)
                    {
                        object value = propertyInfos[i].GetValue(item) ?? null;
                        dataRow.CreateCell(i).SetCellValue(value?.ToString());
                    }
                }
                rowIndex++;
            }

            // 保存Excel文件
            using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                excelDoc.Write(fileStream);
            }


        }


        /// <summary>
        /// 导出实体为文档（指定列)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="t"></param>
        public static void ExportColumn<T>(string filePath, List<T> t, List<string> columns, string SheetName = "Sheet1")
        {
            IWorkbook excelDoc = new XSSFWorkbook();
            //创建一个Sheet
            ISheet excelSheet = excelDoc.CreateSheet(SheetName);

            Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            // 写入表头
            IRow headerRow = excelSheet.CreateRow(0);

            if (columns==null|| columns.Count == 0)
            {
                columns = new List<string>();
                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    DescriptionAttribute columnAttribute = propertyInfos[i].GetCustomAttribute<DescriptionAttribute>();
                    if (columnAttribute != null && !columns.Contains(columnAttribute.Description))
                    {
                        columns.Add(columnAttribute.Description);
                    }
                }
            }     


            int index = 0;
            for (int i = 0; i < propertyInfos.Length; i++)
            {
                DescriptionAttribute columnAttribute = propertyInfos[i].GetCustomAttribute<DescriptionAttribute>();
                if (columnAttribute != null && columns.Contains(columnAttribute.Description))
                {
                    headerRow.CreateCell(index).SetCellValue(columnAttribute.Description);
                    index++;
                }
            }

            // 写入数据
            int rowIndex = 1;
            foreach (var item in t)
            {
                int columIndex = 0;
                IRow dataRow = excelSheet.CreateRow(rowIndex);
                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    DescriptionAttribute columnAttribute = propertyInfos[i].GetCustomAttribute<DescriptionAttribute>();
                    if (columnAttribute != null && columns.Contains(columnAttribute.Description))
                    {
                        object value = propertyInfos[i].GetValue(item) ?? null;
                        dataRow.CreateCell(columIndex).SetCellValue(value?.ToString());
                        columIndex++;
                    }
                }
                rowIndex++;
            }

            // 保存Excel文件
            using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                excelDoc.Write(fileStream);
            }


        }


        /// <summary>
        /// 导出List<Dynamic>为Excel
        /// </summary>
        /// <param name="filePath">文件保存地址</param>
        /// <param name="data">数据列表</param>
        /// <param name="SheetName">Sheet名称</param>
        /// <param name="dicColumNames">可指定列的别名</param>
        public static void Export(string filePath, List<dynamic> data, ObservableCollection<string> classIfyPriority, string SheetName, Dictionary<string, string> dicColumNames = null)
        {
            IWorkbook excelDoc = new XSSFWorkbook();
            //创建一个Sheet
            ISheet excelSheet = excelDoc.CreateSheet(SheetName);
            // 创建表头
            IRow headerRow = excelSheet.CreateRow(0);

            // 获取动态对象的属性名作为表头
            var properties = DynamicCustomeProperty.GetDynamicObjectProperties(data, classIfyPriority);
            int index = 0;
            for (int i = 0; i < properties.Count; i++)
            {
                var cellName = properties[i];
                if (dicColumNames != null && dicColumNames.Any())
                {
                    if (dicColumNames.ContainsKey(cellName))
                        cellName = dicColumNames[cellName];
                }
                headerRow.CreateCell(index).SetCellValue(cellName);
                index++;
            }

            // 填充数据
            int rowIndex = 1;
            foreach (var item in data)
            {
                int columIndex = 0;
                IRow dataRow = excelSheet.CreateRow(rowIndex);
                for (int i = 0; i < properties.Count; i++)
                {
                    var cellName = properties[i];
                    object value = DynamicCustomeProperty.GetPropertyValue(item, cellName) ?? null;
                    dataRow.CreateCell(columIndex).SetCellValue(value?.ToString());
                    columIndex++;
                }
                rowIndex++;
            }

            // 保存Excel文件
            using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                excelDoc.Write(fileStream);
            }



        }

        /// <summary>
        /// 导出List<Dynamic>为Excel
        /// 明细和汇总数据
        /// </summary>
        /// <param name="filePath">文件保存地址</param>
        /// <param name="data">数据列表</param>
        /// <param name="SheetName">Sheet名称</param>
        /// <param name="dicColumNames">可指定列的别名</param>
        public static void Export_copy(string filePath, List<dynamic> data, List<StatisticsClassifyModel> dataSum, ObservableCollection<string> classIfyPriority, string SheetName, Dictionary<string, string> dicColumNames = null)
        {
            IWorkbook excelDoc = new XSSFWorkbook();
            //创建一个Sheet
            ISheet excelSheet = excelDoc.CreateSheet(SheetName);
            // 创建表头
            IRow headerRow = excelSheet.CreateRow(0);

            // 创建字体样式
            IFont headerFont = excelDoc.CreateFont();
            headerFont.Color = IndexedColors.Black.Index; // 设置字体颜色为红色
            headerFont.Boldweight = (short)FontBoldWeight.Bold; // 设置字体加粗

            // 创建单元格样式
            ICellStyle headerStyle = excelDoc.CreateCellStyle();
            headerStyle.SetFont(headerFont);

            // 获取动态对象的属性名作为表头
            var pdataSum = dataSum.OrderBy(w => Array.IndexOf(classIfyPriority.ToArray(), w.ClassIfy)).ToList();
            int index = 0;
            for (int i = 0; i < pdataSum.Count; i++)
            {
                var cellName = pdataSum[i].ClassIfy;
                ICell cell = headerRow.CreateCell(index);
                cell.SetCellValue(cellName);
                cell.CellStyle = headerStyle;
                index++;
            }
            long sumOKandNG = 0;
            for (int i = 0; i < pdataSum.Count; i++)
            {
                var cellName = pdataSum[i].ClassIfy;
                if (cellName == "OK" || cellName == "NG")
                {
                    sumOKandNG += pdataSum[i].Num;
                }
            }
            //填充数据(汇总数据)
            int indexSum = 0;
            IRow dataRowSum = excelSheet.CreateRow(1);
            for (int i = 0; i < pdataSum.Count; i++)
            {
                var cellName = "";
                if (pdataSum[i].ClassIfy == "OK" || pdataSum[i].ClassIfy == "NG")
                {
                    cellName = pdataSum[i].Num + " (总计:" + sumOKandNG + ")" + ", 占比:" + pdataSum[i].Rate;
                }
                else
                {
                    cellName = pdataSum[i].Num + " (总计:" + pdataSum[i].DetailNum + ")" + ", 占比:" + pdataSum[i].Rate;
                }
                ICell cell = dataRowSum.CreateCell(indexSum);
                cell.SetCellValue(cellName);
                indexSum++;
            }

            //创建明细表头
            var properties = DynamicCustomeProperty.GetDynamicObjectProperties(data, classIfyPriority);
            int indexDetail = 0;
            IRow dataRowDetail = excelSheet.CreateRow(4);
            for (int i = 0; i < properties.Count; i++)
            {
                var cellName = properties[i];
                if (dicColumNames != null && dicColumNames.Any())
                {
                    if (dicColumNames.ContainsKey(cellName))
                        cellName = dicColumNames[cellName];
                }
                ICell cell = dataRowDetail.CreateCell(indexDetail);
                cell.SetCellValue(cellName);
                cell.CellStyle = headerStyle;
                indexDetail++;
            }
            // 填充数据
            int rowIndex = 5;
            foreach (var item in data)
            {
                int columIndex = 0;
                IRow dataRow = excelSheet.CreateRow(rowIndex);
                for (int i = 0; i < properties.Count; i++)
                {
                    var cellName = properties[i];
                    object value = DynamicCustomeProperty.GetPropertyValue(item, cellName) ?? null;
                    dataRow.CreateCell(columIndex).SetCellValue(value?.ToString());
                    columIndex++;
                }
                rowIndex++;
            }

            // 保存Excel文件
            using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                excelDoc.Write(fileStream);
            }
        }


        /// <summary>
        /// 导出List<Dynamic>为Excel
        /// 明细和汇总数据
        /// </summary>
        /// <param name="filePath">文件保存地址</param>
        /// <param name="data">数据列表</param>
        /// <param name="SheetName">Sheet名称</param>
        /// <param name="dicColumNames">可指定列的别名</param>
        public static string Export(string filePath, List<dynamic> data, List<StatisticsClassifyModel> dataSum, ObservableCollection<string> classIfyPriority, string SheetName, Dictionary<string, string> dicColumNames = null)
        {
            try
            {
                IWorkbook excelDoc = new XSSFWorkbook();
                //创建一个Sheet
                ISheet excelSheet = excelDoc.CreateSheet(SheetName);
                // 创建表头
                IRow headerRow = excelSheet.CreateRow(0);

                // 创建字体样式
                IFont headerFont = excelDoc.CreateFont();
                headerFont.Color = IndexedColors.Black.Index; // 设置字体颜色为黑色
                headerFont.Boldweight = (short)FontBoldWeight.Bold; // 设置字体加粗

                // 创建单元格样式
                ICellStyle headerStyle = excelDoc.CreateCellStyle();
                headerStyle.SetFont(headerFont);

                // 获取动态对象的属性名作为表头
                var pdataSum = dataSum.OrderBy(w => Array.IndexOf(classIfyPriority.ToArray(), w.ClassIfy)).ToList();
                long sumOKandNG = 0;
                long sumOK = 0;
                int indexhead = 0;
                //汇总数据
                for (int i = 0; i < pdataSum.Count; i++)
                {
                    var cellName = pdataSum[i].ClassIfy;
                    ICell cell = headerRow.CreateCell(indexhead);
                    if (cellName == "OK")
                    {
                        sumOKandNG += pdataSum[i].Num;
                        cellName = "OK数量";
                        cell.SetCellValue(cellName);
                        cell.CellStyle = headerStyle;
                        indexhead++;
                    }
                    else if (cellName == "NG")
                    {
                        sumOKandNG += pdataSum[i].Num;
                        cellName = "NG数量";
                        cell = headerRow.CreateCell(indexhead);
                        cell.SetCellValue(cellName);
                        cell.CellStyle = headerStyle;
                        indexhead++;
                    }
                }
                ICell headcell3 = headerRow.CreateCell(indexhead);
                headcell3.SetCellValue("总数");
                headcell3.CellStyle = headerStyle;
                indexhead++;
                ICell headcell4 = headerRow.CreateCell(indexhead);
                headcell4.SetCellValue("优率");
                headcell4.CellStyle = headerStyle;
                indexhead++;

                //填充数据(汇总数据)
                int indexSum = 0;
                IRow dataRowSum = excelSheet.CreateRow(1);
                for (int i = 0; i < pdataSum.Count; i++)
                {
                    long cellName = 0;
                    if (pdataSum[i].ClassIfy == "OK")
                    {
                        cellName = pdataSum[i].Num;
                        sumOK = cellName;
                        ICell cell = dataRowSum.CreateCell(indexSum);
                        cell.SetCellValue(cellName);
                        indexSum++;
                    }
                    else if (pdataSum[i].ClassIfy == "NG")
                    {
                        cellName = pdataSum[i].Num;
                        ICell cell = dataRowSum.CreateCell(indexSum);
                        cell.SetCellValue(cellName);
                        indexSum++;
                    }

                }
                ICell cel3 = dataRowSum.CreateCell(indexSum);
                cel3.SetCellValue(sumOKandNG);
                indexSum++;
                ICell cel4 = dataRowSum.CreateCell(indexSum);
                var sumRate = string.Format("{0:P}", (double)sumOK / (sumOKandNG == 0 ? 1 : sumOKandNG));
                cel4.SetCellValue(sumRate);
                indexSum++;
                //汇总明细表头
                //IRow dataRow4 = excelSheet.CreateRow(4);
                //ICell dataRow4cell2 = dataRow4.CreateCell(1);
                //dataRow4cell2.SetCellValue("数量");
                //dataRow4cell2.CellStyle = headerStyle;

                //ICell dataRow4cell3 = dataRow4.CreateCell(2);
                //dataRow4cell3.SetCellValue("总计");
                //dataRow4cell3.CellStyle = headerStyle;

                //ICell dataRow4cell4 = dataRow4.CreateCell(3);
                //dataRow4cell4.SetCellValue("占比");
                //dataRow4cell4.CellStyle = headerStyle;

                //改为第一行表头
                var irow0 = excelSheet.GetRow(0);
                int firstBlockNumber = indexhead--;
                //两块之间的距离
                int colSpan = 2;
                #region Edit汇总
                ICell dataRow4cell2 = irow0.CreateCell(firstBlockNumber+colSpan+1);
                dataRow4cell2.SetCellValue("数量");
                dataRow4cell2.CellStyle = headerStyle;

                ICell dataRow4cell3 = irow0.CreateCell(firstBlockNumber + colSpan+2);
                dataRow4cell3.SetCellValue("总计");
                dataRow4cell3.CellStyle = headerStyle;

                ICell dataRow4cell4 = irow0.CreateCell(firstBlockNumber + colSpan + 3);
                dataRow4cell4.SetCellValue("占比");
                dataRow4cell4.CellStyle = headerStyle;
                

                //汇总明细数据填充
                //int index5 = 5;
                //for (int i = 0; i < pdataSum.Count; i++)
                //{
                //    IRow dataRow = excelSheet.CreateRow(index5);
                //    var cellName = pdataSum[i].ClassIfy;
                //    if (cellName == "OK" || cellName == "NG")
                //    {
                //        continue;
                //    }
                //    ICell cell0 = dataRow.CreateCell(0);
                //    cell0.SetCellValue(cellName);
                //    cell0.CellStyle = headerStyle;

                //    ICell cell1 = dataRow.CreateCell(1);
                //    cell1.SetCellValue(pdataSum[i].Num);

                //    ICell cell2 = dataRow.CreateCell(2);
                //    cell2.SetCellValue(pdataSum[i].DetailNum);

                //    ICell cell3 = dataRow.CreateCell(3);
                //    cell3.SetCellValue(pdataSum[i].Rate);
                //    index5++;
                //}
                int block1_index = 1;
                for (int i = 0; i < pdataSum.Count; i++)
                {
                    IRow dataRow = excelSheet.GetRow(block1_index)?? excelSheet.CreateRow(block1_index);
                    var cellName = pdataSum[i].ClassIfy;
                    if (cellName == "OK" || cellName == "NG")
                    {
                        continue;
                    }
                    ICell cell0 = dataRow.CreateCell(firstBlockNumber + colSpan+0);
                    cell0.SetCellValue(cellName);
                    cell0.CellStyle = headerStyle;

                    ICell cell1 = dataRow.CreateCell(firstBlockNumber + colSpan + 1);
                    cell1.SetCellValue(pdataSum[i].Num);

                    ICell cell2 = dataRow.CreateCell(firstBlockNumber + colSpan + 2);
                    cell2.SetCellValue(pdataSum[i].DetailNum);

                    ICell cell3 = dataRow.CreateCell(firstBlockNumber + colSpan + 3);
                    cell3.SetCellValue(pdataSum[i].Rate);
                    block1_index++;
                }
                #endregion

                //创建详细信息表头
                //var properties = DynamicCustomeProperty.GetDynamicObjectProperties(data, classIfyPriority);
                //int indexDetail = 0;
                //IRow dataRowDetail = excelSheet.CreateRow(20);
                //for (int i = 0; i < properties.Count; i++)
                //{
                //    var cellName = properties[i];
                //    if (dicColumNames != null && dicColumNames.Any())
                //    {
                //        if (dicColumNames.ContainsKey(cellName))
                //            cellName = dicColumNames[cellName];
                //    }
                //    if (cellName == "NG")
                //    {
                //        continue;
                //    }
                //    ICell cell = dataRowDetail.CreateCell(indexDetail);
                //    cell.SetCellValue(cellName);
                //    cell.CellStyle = headerStyle;
                //    indexDetail++;
                //}
                // 填充详细信息数据
                //int rowIndex = 21;
                //foreach (var item in data)
                //{
                //    int columIndex = 0;
                //    IRow dataRow = excelSheet.CreateRow(rowIndex);
                //    for (int i = 0; i < properties.Count; i++)
                //    {
                //        var cellName = properties[i];
                //        object value = DynamicCustomeProperty.GetPropertyValue(item, cellName) ?? null;
                //        if (cellName == "NG")
                //        {
                //            continue;
                //        }
                //        dataRow.CreateCell(columIndex).SetCellValue(value?.ToString());
                //        columIndex++;
                //    }
                //    rowIndex++;
                //}

                #region 详细数据块Edit
                var properties = DynamicCustomeProperty.GetDynamicObjectProperties(data, classIfyPriority);

                //block1.2有个空cell表头
                int secondBlockNumber = indexSum;
                int block2_index = 0;
                
                for (int i = 0; i < properties.Count; i++)
                {
                    var cellName = properties[i];
                    if (dicColumNames != null && dicColumNames.Any())
                    {
                        if (dicColumNames.ContainsKey(cellName))
                            cellName = dicColumNames[cellName];
                    }
                    if (cellName == "NG")
                    {
                        continue;
                    }
                    ICell cell = irow0.CreateCell(firstBlockNumber + secondBlockNumber + 2*colSpan+block2_index);
                    cell.SetCellValue(cellName);
                    cell.CellStyle = headerStyle;
                    block2_index++;
                }
                // 填充详细信息数据              

                int rowIndex = 1;
                foreach (var item in data)
                {
                    int columIndex = 0;
                    IRow dataRow = excelSheet.GetRow(rowIndex)?? excelSheet.CreateRow(rowIndex);
                    for (int i = 0; i < properties.Count; i++)
                    {
                        var cellName = properties[i];
                        object value = DynamicCustomeProperty.GetPropertyValue(item, cellName) ?? null;
                        if (cellName == "NG")
                        {
                            continue;
                        }
                        dataRow.CreateCell(firstBlockNumber+secondBlockNumber+ 2 * colSpan + columIndex).SetCellValue(value?.ToString());
                        columIndex++;
                    }
                    rowIndex++;
                }
                //Freeze
                excelSheet.CreateFreezePane(0, 1);
                #endregion

                // 保存Excel文件
                using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    excelDoc.Write(fileStream);
                }
                return "";
            }
            catch (Exception e)
            {
                return e.Message.ToString();
            }
        }
    }
}
